"""
商谈管理路由
"""
import io
import json
import os
from datetime import datetime, timedelta
from flask import Blueprint, render_template, request, redirect, url_for, flash, send_file, current_app
from flask_login import login_required, current_user
from sqlalchemy import func
from werkzeug.utils import secure_filename
import xlsxwriter
import openpyxl

from app.extensions import db
from app.models.customer import Customer
from app.models.staff import Staff
from app.models.negotiation import Negotiation, UploadException

negotiation_bp = Blueprint('negotiation', __name__, url_prefix='/negotiation')

# 配置上传文件夹
ALLOWED_EXTENSIONS = {'xlsx', 'xls', 'csv'}


def allowed_file(filename):
    return '.' in filename and filename.rsplit('.', 1)[1].lower() in ALLOWED_EXTENSIONS


@negotiation_bp.route('/')
@negotiation_bp.route('/list')
@login_required
def negotiation_list():
    """合作商谈列表页面"""
    page = request.args.get('page', 1, type=int)
    search_staff = request.args.get('search_staff', '')
    search_customer = request.args.get('search_customer', '')
    search_category = request.args.get('search_category', '')
    search_start_date = request.args.get('search_start_date', '')
    search_end_date = request.args.get('search_end_date', '')
    
    query = Negotiation.query
    
    if search_staff:
        query = query.filter(Negotiation.staff_names.contains(search_staff))
    if search_customer:
        query = query.filter(Negotiation.customer_name.contains(search_customer))
    if search_category:
        query = query.filter(Negotiation.category == search_category)
    if search_start_date:
        start_date = datetime.strptime(search_start_date, '%Y-%m-%d')
        query = query.filter(Negotiation.start_time >= start_date)
    if search_end_date:
        end_date = datetime.strptime(search_end_date, '%Y-%m-%d')
        query = query.filter(Negotiation.end_time <= end_date + timedelta(days=1))
    
    negotiations = query.order_by(Negotiation.start_time.desc()).paginate(page=page, per_page=20, error_out=False)
    
    # 获取下拉选项数据
    staff_names = db.session.query(Staff.name).all()
    customer_names = db.session.query(Customer.name).all()
    project_numbers = db.session.query(Customer.project_number).distinct().all()
    
    return render_template('negotiation/negotiation_list.html',
                         negotiations=negotiations,
                         staff_names=[s[0] for s in staff_names],
                         customer_names=[c[0] for c in customer_names],
                         project_numbers=[p[0] for p in project_numbers if p[0]],
                         search_staff=search_staff,
                         search_customer=search_customer,
                         search_category=search_category,
                         search_start_date=search_start_date,
                         search_end_date=search_end_date)


@negotiation_bp.route('/add', methods=['GET', 'POST'])
@login_required
def add_negotiation():
    """添加商谈记录"""
    if request.method == 'POST':
        try:
            # 解析时间
            start_time = datetime.strptime(request.form.get('start_time'), '%Y-%m-%dT%H:%M')
            end_time = datetime.strptime(request.form.get('end_time'), '%Y-%m-%dT%H:%M')
            
            # 计算时长
            duration_minutes = int((end_time - start_time).total_seconds() / 60)
            
            negotiation = Negotiation(
                customer_name=request.form.get('customer_name'),
                project_number=request.form.get('project_number'),
                category=request.form.get('category'),
                staff_names=request.form.get('staff_names'),
                project_name=request.form.get('project_name'),
                start_time=start_time,
                end_time=end_time,
                duration_minutes=duration_minutes,
                notes=request.form.get('notes'),
                is_cross_project=len(request.form.get('staff_names', '').split('、')) > 1
            )
            
            db.session.add(negotiation)
            db.session.commit()
            flash('商谈记录添加成功')
            return redirect(url_for('negotiation.negotiation_list'))
        except Exception as e:
            db.session.rollback()
            flash(f'添加失败: {str(e)}')
    
    # GET请求，显示添加表单
    customers = Customer.query.all()
    staff_list = Staff.query.all()
    return render_template('negotiation/add_negotiation.html', 
                         customers=customers, 
                         staff_list=staff_list)


@negotiation_bp.route('/download_template')
@login_required
def download_template():
    """下载商谈数据上传模板"""
    output = io.BytesIO()
    workbook = xlsxwriter.Workbook(output)
    worksheet = workbook.add_worksheet('商谈数据模板')
    
    # 表头
    headers = [
        '所属项目', '类别', '客户姓名', '项目序号', '项目负责人', 
        '开始商谈时间（每次）', '结束商谈时间（每次）', '备注'
    ]
    
    # 示例数据
    sample_data = [
        ['XXX项目', '线上合作商谈', 'XXX客户', '123', '张三、李四、王五', 
         '2025/02/22 10:16:32', '2025/02/22 12:16:32', '备注信息']
    ]
    
    # 设置表头格式
    header_format = workbook.add_format({
        'bold': True,
        'bg_color': '#D7E4BC',
        'border': 1,
        'text_wrap': True
    })
    
    # 写入表头
    for col, header in enumerate(headers):
        worksheet.write(0, col, header, header_format)
    
    # 写入示例数据
    for row, data in enumerate(sample_data, 1):
        for col, value in enumerate(data):
            worksheet.write(row, col, value)
    
    # 调整列宽
    worksheet.set_column('A:H', 18)
    
    # 添加说明
    worksheet.write(3, 0, '说明：')
    worksheet.write(4, 0, '1. 项目负责人可以填写多个，用中文逗号分隔')
    worksheet.write(5, 0, '2. 时间格式：YYYY/MM/DD HH:MM:SS')
    worksheet.write(6, 0, '3. 类别只能填：线上合作商谈 或 线下合作商谈')
    worksheet.write(7, 0, '4. 客户姓名支持中文、英文、数字')
    
    workbook.close()
    output.seek(0)
    
    return send_file(
        output,
        as_attachment=True,
        download_name=f'商谈数据上传模板_{datetime.now().strftime("%Y%m%d")}.xlsx',
        mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
    )


@negotiation_bp.route('/upload', methods=['POST'])
@login_required
def upload_negotiations():
    """批量上传商谈数据"""
    if 'upload_file' not in request.files:
        flash('未选择文件')
        return redirect(url_for('negotiation.negotiation_list'))
    
    file = request.files['upload_file']
    if file.filename == '':
        flash('未选择文件')
        return redirect(url_for('negotiation.negotiation_list'))
    
    if not file.filename.lower().endswith(('.xlsx', '.xls')):
        flash('只支持Excel格式文件（.xlsx或.xls）')
        return redirect(url_for('negotiation.negotiation_list'))
    
    try:
        # 保存上传的文件
        filename = secure_filename(file.filename)
        upload_folder = current_app.config.get('UPLOAD_FOLDER', 'uploads')
        if not os.path.exists(upload_folder):
            os.makedirs(upload_folder)
        filepath = os.path.join(upload_folder, filename)
        file.save(filepath)
        
        # 解析Excel文件
        workbook = openpyxl.load_workbook(filepath)
        worksheet = workbook.active
        
        success_count = 0
        error_count = 0
        error_details = []
        
        # 从第2行开始读取数据（第1行是表头）
        for row_num, row in enumerate(worksheet.iter_rows(min_row=2, values_only=True), 2):
            if not any(row):  # 跳过空行
                continue
                
            try:
                # 解析数据
                project_name = str(row[0]) if row[0] else ''
                category = str(row[1]) if row[1] else ''
                customer_name = str(row[2]) if row[2] else ''
                project_number = str(row[3]) if row[3] else ''
                staff_names_str = str(row[4]) if row[4] else ''
                start_time_str = str(row[5]) if row[5] else ''
                end_time_str = str(row[6]) if row[6] else ''
                notes = str(row[7]) if row[7] else ''
                
                # 验证必填字段
                if not all([customer_name, category, staff_names_str, start_time_str, end_time_str]):
                    error_details.append(f'第{row_num}行：缺少必填字段')
                    error_count += 1
                    continue
                
                # 验证类别
                if category not in ['线上合作商谈', '线下合作商谈']:
                    error_details.append(f'第{row_num}行：类别必须是"线上合作商谈"或"线下合作商谈"')
                    error_count += 1
                    continue
                
                # 解析时间
                try:
                    start_time = datetime.strptime(start_time_str.strip(), '%Y/%m/%d %H:%M:%S')
                    end_time = datetime.strptime(end_time_str.strip(), '%Y/%m/%d %H:%M:%S')
                except ValueError:
                    error_details.append(f'第{row_num}行：时间格式错误，应为YYYY/MM/DD HH:MM:SS')
                    error_count += 1
                    continue
                
                # 计算时长
                duration_minutes = int((end_time - start_time).total_seconds() / 60)
                if duration_minutes <= 0:
                    error_details.append(f'第{row_num}行：结束时间必须晚于开始时间')
                    error_count += 1
                    continue
                
                # 创建商谈记录
                negotiation = Negotiation(
                    customer_name=customer_name,
                    project_number=project_number,
                    category=category,
                    staff_names=staff_names_str,
                    project_name=project_name,
                    start_time=start_time,
                    end_time=end_time,
                    duration_minutes=duration_minutes,
                    notes=notes,
                    is_cross_project=len(staff_names_str.split('、')) > 1  # 多个负责人认为是跨项目
                )
                
                db.session.add(negotiation)
                success_count += 1
                
            except Exception as e:
                error_details.append(f'第{row_num}行：处理错误 - {str(e)}')
                error_count += 1
        
        # 提交成功的记录
        if success_count > 0:
            db.session.commit()
        
        # 记录异常数据
        if error_count > 0:
            exception_record = UploadException(
                filename=filename,
                status='待处理',
                uploader=current_user.username,
                upload_time=datetime.utcnow(),
                error_data=json.dumps(error_details[:10]),  # 只保存前10个错误
                error_reason=f'共{error_count}条数据处理失败'
            )
            db.session.add(exception_record)
            db.session.commit()
        
        # 清理临时文件
        os.remove(filepath)
        
        flash(f'上传完成！成功：{success_count}条，失败：{error_count}条')
        
    except Exception as e:
        db.session.rollback()
        flash(f'上传失败：{str(e)}')
    
    return redirect(url_for('negotiation.negotiation_list'))


@negotiation_bp.route('/exceptions')
@login_required
def upload_exceptions():
    """上传异常数据管理"""
    page = request.args.get('page', 1, type=int)
    status_filter = request.args.get('status', '')
    
    query = UploadException.query
    
    if status_filter:
        query = query.filter(UploadException.status == status_filter)
    
    exceptions = query.order_by(UploadException.upload_time.desc()).paginate(page=page, per_page=20, error_out=False)
    
    return render_template('negotiation/upload_exceptions.html',
                         exceptions=exceptions,
                         status_filter=status_filter)


@negotiation_bp.route('/view_exception/<int:exception_id>')
@login_required
def view_exception_data(exception_id):
    """查看异常数据详情"""
    exception = UploadException.query.get_or_404(exception_id)
    
    # 解析异常数据
    error_data = []
    if exception.error_data:
        try:
            error_data = json.loads(exception.error_data)
        except:
            error_data = [exception.error_data]
    
    return render_template('negotiation/view_exception_data.html',
                         exception=exception,
                         error_data=error_data)


@negotiation_bp.route('/mark_processed', methods=['POST'])
@login_required
def mark_exception_processed():
    """标记异常为已处理"""
    exception_ids = request.form.getlist('exception_ids')
    
    if not exception_ids:
        flash('请选择要处理的异常记录')
        return redirect(url_for('negotiation.upload_exceptions'))
    
    try:
        for exception_id in exception_ids:
            exception = UploadException.query.get(exception_id)
            if exception:
                exception.status = '已处理'
                exception.processor = current_user.username
                exception.process_time = datetime.utcnow()
        
        db.session.commit()
        flash(f'已成功处理 {len(exception_ids)} 条异常记录')
    except Exception as e:
        db.session.rollback()
        flash(f'处理失败: {str(e)}')
    
    return redirect(url_for('negotiation.upload_exceptions'))


@negotiation_bp.route('/export')
@login_required
def export_negotiations():
    """导出商谈数据"""
    negotiations = Negotiation.query.all()
    
    # 创建Excel文件
    output = io.BytesIO()
    workbook = xlsxwriter.Workbook(output)
    worksheet = workbook.add_worksheet('商谈记录')
    
    # 写入表头
    headers = ['客户姓名', '项目序号', '商谈类别', '项目负责人', '所属项目', 
               '开始时间', '结束时间', '总时长(分钟)', '跨项目', '备注']
    
    # 设置表头格式
    header_format = workbook.add_format({
        'bold': True,
        'bg_color': '#D7E4BC',
        'border': 1
    })
    
    for col, header in enumerate(headers):
        worksheet.write(0, col, header, header_format)
    
    # 写入数据
    for row, negotiation in enumerate(negotiations, 1):
        worksheet.write(row, 0, negotiation.customer_name or '')
        worksheet.write(row, 1, negotiation.project_number or '')
        worksheet.write(row, 2, negotiation.category or '')
        worksheet.write(row, 3, negotiation.staff_names or '')
        worksheet.write(row, 4, negotiation.project_name or '')
        worksheet.write(row, 5, negotiation.start_time.strftime('%Y-%m-%d %H:%M') if negotiation.start_time else '')
        worksheet.write(row, 6, negotiation.end_time.strftime('%Y-%m-%d %H:%M') if negotiation.end_time else '')
        worksheet.write(row, 7, negotiation.duration_minutes or 0)
        worksheet.write(row, 8, '是' if negotiation.is_cross_project else '否')
        worksheet.write(row, 9, negotiation.notes or '')
    
    # 调整列宽
    worksheet.set_column('A:J', 15)
    
    workbook.close()
    output.seek(0)
    
    return send_file(
        output,
        as_attachment=True,
        download_name=f'商谈记录_{datetime.now().strftime("%Y%m%d")}.xlsx',
        mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
    )


@negotiation_bp.route('/edit/<int:negotiation_id>', methods=['POST'])
@login_required
def edit_negotiation(negotiation_id):
    """编辑商谈记录"""
    negotiation = Negotiation.query.get_or_404(negotiation_id)
    try:
        # 解析时间
        start_time = datetime.strptime(request.form.get('start_time'), '%Y-%m-%dT%H:%M')
        end_time = datetime.strptime(request.form.get('end_time'), '%Y-%m-%dT%H:%M')
        
        # 计算时长
        duration_minutes = int((end_time - start_time).total_seconds() / 60)
        
        # 更新记录
        negotiation.customer_name = request.form.get('customer_name')
        negotiation.project_number = request.form.get('project_number')
        negotiation.category = request.form.get('category')
        negotiation.staff_names = request.form.get('staff_names')
        negotiation.project_name = request.form.get('project_name')
        negotiation.start_time = start_time
        negotiation.end_time = end_time
        negotiation.duration_minutes = duration_minutes
        negotiation.notes = request.form.get('notes')
        negotiation.is_cross_project = len(request.form.get('staff_names', '').split('、')) > 1
        
        db.session.commit()
        flash('商谈记录更新成功')
    except Exception as e:
        db.session.rollback()
        flash(f'更新失败: {str(e)}')
    
    return redirect(url_for('negotiation.negotiation_list'))


@negotiation_bp.route('/delete/<int:negotiation_id>', methods=['POST'])
@login_required
def delete_negotiation(negotiation_id):
    """删除商谈记录"""
    negotiation = Negotiation.query.get_or_404(negotiation_id)
    try:
        db.session.delete(negotiation)
        db.session.commit()
        flash('商谈记录删除成功')
    except Exception as e:
        db.session.rollback()
        flash(f'删除失败: {str(e)}')
    
    return redirect(url_for('negotiation.negotiation_list'))
